create proc [dbo].[p_AverageDailyBlockCount](
	@DateBeginning datetime, 
	@DatabaseID bigint) AS  

set nocount on

select
	datepart(day,a.[CreateDate]) 'iDay',
	datepart(month,a.[CreateDate]) 'iMonth',
	datepart(year,a.[CreateDate]) 'iYear',
	count(*) 'CountOfBlocks',  
	count(distinct a.SessionID) 'CountOfSessions'
into
	#x 
from  	
	BlockingHistory a  		
where  
	a.[DatabaseID]=@DatabaseID
	and a.createdate between @DateBeginning and dateadd(day,7,@DateBeginning)

group by
	datepart(day,a.[CreateDate]),
	datepart(month,a.[CreateDate]),
	datepart(year,a.[CreateDate])

declare @week table(
		dtWeekDate datetime,
		iDay int,
		iMonth int,
		iYear int,
		iBlockCount int)   
 
declare @iCurDay int,  		
	@dtCurDate datetime,
	@iBlockCount int  

set @iCurDay=0  
while @iCurDay<7
begin  	 	
	set @dtCurDate=dateadd(day,@iCurDay,@DateBeginning)  	

	select 
		@iBlockCount=count(*) 
	from 
		BlockingHistory a 
	where 
		a.[CreateDate] between @dtCurDate and dateadd(day,1,@dtCurDate)  
		and a.[DatabaseID]=@DatabaseID

	insert into @week(
		dtWeekDate,
		iDay,
		iMonth,
		iYear,
		iBlockCount) values(

		@dtCurDate,
		datepart(day,@dtCurDate),
		datepart(month,@dtCurDate),
		datepart(year,@dtCurDate),
		@iBlockCount)  	

	set @iCurDay=@iCurDay+1  
end    

select   	
	a.dtWeekDate 'dtWeekDate',
	case 
		when b.CountOfSessions is null then 0
		else convert(decimal(18,6),isnull(convert(decimal(18,6),b.CountOfBlocks),0)/isnull(b.CountOfSessions,0))
	end 'AvgCountOfAllBlocks'
from   	
	@week a  	
		left outer join #x b on b.iDay=a.iDay
								and b.iMonth=a.iMonth	 
								and b.iYear=a.iYear	  
order by   	
	a.dtWeekDate

drop table #x





